        package DAL;

        import java.util.*;
        
        import DBUtil.SQLUtil;
        
        public class Score {

		//获取成绩表信息列表
		public List<Object> getEntity()
		{
			String sqlCmd="select *from V_Score  ORDER BY MathId";
			return DBUtil.SQLUtil.executeQuery(sqlCmd, null);//执行查询操作executeQuery
		}
            
        //获取分页后成绩表信息列表
		public List<Object> getEntity(int page)
		{
			int size=(page-1)*15;
			String sqlCmd="select *from V_Score ORDER BY MathId  limit "+size+",15";
			return DBUtil.SQLUtil.executeQuery(sqlCmd, null);//执行查询操作executeQuery
		}
		
		//获取单项比赛最高分数据
		public List<Object> getTop(String MathId,String MathTag)
		{
			String sqlCmd;
			if(MathTag.equals("0"))
			{
				sqlCmd="SELECT *FROM v_score WHERE MathId='"+MathId+"'  AND mark=(SELECT Min(mark) FROM v_score WHERE MathId='"+MathId+"' )";
			}
			else {
				sqlCmd="SELECT *FROM v_score WHERE MathId='"+MathId+"'  AND mark=(SELECT MAX(mark) FROM v_score WHERE MathId='"+MathId+"' )";
			}
			
			return DBUtil.SQLUtil.executeQuery(sqlCmd, null);//执行查询操作executeQuery
		}
                
        //根据查询条件sqlWhere获取分页后成绩表信息列表
		public List<Object> getEntityByWhere(String sqlWhere,int page)
		{
			int size=(page-1)*15;
			String sqlCmd="select *from V_Score where "+sqlWhere+" limit "+ size+",15";
			return DBUtil.SQLUtil.executeQuery(sqlCmd, null);//执行查询操作executeQuery
        }
		
		//根据查询条件sqlWhere获取分页后成绩表信息列表
		public List<Object> getEntityOrderBy(String sqlWhere,String rankby)
		{
			String sqlCmd;
			if(rankby.equals("time")){
				sqlCmd="SELECT (@rowNum:=@rowNum+1) AS rowNo,MathName,PlayerName,PName,Mark,MathTag,grade FROM V_score, (SELECT (@rowNum :=0) ) b WHERE "+sqlWhere+" ORDER BY V_Score.`mark` ASC";
				//sqlCmd="select *from V_Score where "+sqlWhere;
			}
			else {
				sqlCmd="SELECT (@rowNum:=@rowNum+1) AS rowNo,MathName,PlayerName,PName,Mark,MathTag,grade FROM V_score, (SELECT (@rowNum :=0) ) b WHERE "+sqlWhere+" ORDER BY V_Score.`mark` DESC";
			}
			
			return DBUtil.SQLUtil.executeQuery(sqlCmd, null);//执行查询操作executeQuery
        }
        
        //删除成绩表信息
        public int deleteEntity(String ScoreId)
        {
            String sqlCmd="delete from Score where ScoreId='"+ScoreId+"'";
            return DBUtil.SQLUtil.executeNonQuery(sqlCmd, null);//执行非查询操作executeNonQuery
        }
        
        //根据成绩表编号获取成绩表信息
        public List<Object> getEntityById(String ScoreId)
        {
            String sqlCmd="select *From V_Score where ScoreId='"+ScoreId+"'";
            return DBUtil.SQLUtil.executeQuery(sqlCmd, null);//执行查询操作executeQuery
        }
        
        //更新成绩表信息
        public int updateEntity(String ScoreId,String SchId,String mark,String grade)
        {
            String sqlCmd="Update Score set SchId='" + SchId + "',mark='" + mark + "',grade='"+grade+"' where ScoreId='"+ScoreId+"'";
            return SQLUtil.executeNonQuery(sqlCmd, null);
        }
        
        //插入成绩表信息
        public int insertEntity(String ScoreId,String SchId,String mark,String grade)
        {
            String sqlCmd="Insert into Score values('" + ScoreId + "','" + SchId + "','"+mark+"','"+grade+"')";
            return SQLUtil.executeNonQuery(sqlCmd, null);
        }
        
        //检查插入主键是否重复
        public boolean checkExist(String ScoreId)
        {
            String sqlCmd="select count(*) from Score where ScoreId='"+ScoreId+"'";
            if(1==Integer.parseInt(SQLUtil.excuteScalar(sqlCmd, null).toString()) )
            {
                return true;
            }
            return false;
        }

		//获取分页总数
		public Object getPageCount()
		{
			String sqlCmd="SELECT CEIL( COUNT(*)/15.0) FROM V_Score ";
			return SQLUtil.excuteScalar(sqlCmd, null);
		}

		//根据查询条件获取分页总数
		public Object getPageCountByWhere(String sqlWhere)
		{
			String sqlCmd="SELECT CEIL( COUNT(*)/15.0) FROM V_Score where "+sqlWhere;
			return SQLUtil.excuteScalar(sqlCmd, null);
		}
		
     }
